package dataBase.magaers;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.SortedMap;
import java.util.SortedSet;
import java.util.TreeMap;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.validation.Schema;

import org.eclipse.jdt.internal.compiler.codegen.CodeStream;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

import com.mysql.jdbc.MysqlErrorNumbers;

import dataBase.CidTitleVersion;
import dataBase.Code;
import dataBase.CodeCategoriesTable;
import dataBase.CodeTable;
import dataBase.CodeTitleTable;
import dataBase.ConnectionManager;
import dataBase.DBInitializer;
import dataBase.DBNotInitializedException;
import dataBase.ISqlTable;
import dataBase.Version;
import dataBase.VersionUidLangCid;

import servlets.ErrorCode;

public class CodeDBMngr {
	private static CodeDBMngr instance = null;

	private ISqlTable codeCategoriesTable = null;
	private ISqlTable codeTable = null;
	private ISqlTable codeTitleTable = null;
	
	private ConnectionManager connectionManager = null;
	private DBInitializer initializer = null;
	
	public static final int ITEMS_IN_PAGE = 5;
	private CodeDBMngr() throws DBNotInitializedException, ClassNotFoundException{
		// singleton pattern
		initializer = DBInitializer.getInstance();
		connectionManager = ConnectionManager.getInstance();
		
		codeCategoriesTable = initializer.getCodeCategoriesTable();
		codeTable = initializer.getCodeTable();
		codeTitleTable = initializer.getCodeTitleTable();
	}
	
	public static CodeDBMngr getInstance(){
		if (instance == null){
			try {
				instance = new CodeDBMngr();
			} catch (DBNotInitializedException e) {
				return null;
			} catch (ClassNotFoundException e) {
			}
		}
		return instance;
	}
	public ErrorCode addNewCode(Code toAdd){
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_SERIALIZABLE);
		if (stmt == null){
			return ErrorCode.CANNOT_CONNECT_TO_DB;
		}
		boolean newVersion = false;
		String scid = ((CodeTitleTable)codeTitleTable).searchByTitle(stmt, toAdd.getTitle());
		if (scid != null){
			newVersion = true;
		}
		else {
			scid = "c" + String.valueOf(Calendar.getInstance().getTimeInMillis());// that's how we make sure unique code id
		}
		toAdd.setCodeId(scid); 

		// insert into code-title table
		List<String> codeTitleValues = new LinkedList<String>();
		codeTitleValues.add(scid);
		codeTitleValues.add(toAdd.getTitle());
		
		// insert into code-categories table
		List<String> categories = toAdd.getCategories();
		
		// insert into codes table
		List<String> codesValues = new LinkedList<String>();
		codesValues.add(scid);
		codesValues.add(String.valueOf(toAdd.getMajorVer())); 
		codesValues.add(toAdd.getUid());
		codesValues.add(toAdd.getLanguage());
		codesValues.add(toAdd.getBody());
		codesValues.add(String.valueOf(toAdd.isVisible()));
		
		try {
			// insert code-title
			codeTitleTable.insertRecord(stmt, codeTitleValues, newVersion);

			// insert code-category
			for (int i = 0 ; i < categories.size() ; i++){
				List<String> codeCategoryValue = new LinkedList<String>();
				codeCategoryValue.add(scid);
				codeCategoryValue.add(categories.get(i));
				
				codeCategoriesTable.insertRecord(stmt, codeCategoryValue, newVersion);
			}	
			
			// insert code
			codeTable.insertRecord(stmt, codesValues);
			
			connectionManager.commitAndClose(stmt);	
			
		} catch (SQLException e) {
			connectionManager.rollbackAndClose(stmt);
			return ErrorCode.CODE_CREATE_ERROR;
		}
		return ErrorCode.NO_ERROR;
	}
	
	public Map<String,String> search(String category, String user, String inTitle,
								String inCode, String language, boolean allVersions){
		Map<String,String> result = new HashMap<String,String>();
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt == null){
			return null;
		}
		// SELECT part
		String select = "SELECT DISTINCT CT.CID AS cid, CT.title AS title";
		
		// FROM & WHERE part
		String from = " FROM codesharingdb.codetitles CT ";
		String where = " WHERE";
		if (category != null) {
			from += ", codesharingdb.codecategories CC";
			where += " CC.cid =CT.CID";
			where += " AND MATCH(CC.category) AGAINST('" + category + "' IN BOOLEAN MODE)";
		}
		if ((user != null) || (inCode != null) || (language != null) ) {
			from += ", codesharingdb.codes CODES";
			if (category != null) {
				where += " AND";
			}
			where += " CODES.cid =CT.CID";
			if (user != null){
				where += " AND CODES.uid = '" + user + "'";
			}
			if (inCode != null){
				where += " AND CODES.body LIKE '%" + inCode + "%'";
			}
			if (language != null){
				where += " AND CODES.Language = '" + language + "'";
			}
		}
		if (inTitle != null) {
			// CT is already in from section
			if (category != null || user != null) {
				where += " AND";
			}
			where += " CT.title LIKE '%" + inTitle + "%'";
		}		

		String query = select + from + where + "GROUP BY title" + ";" ; 
		ResultSet rs;
		try {
			rs = stmt.executeQuery(query);
			while(rs.next()){
				result.put((String)rs.getObject("cid"), (String)rs.getObject("title"));
			}
		} catch (SQLException e) {
			connectionManager.rollbackAndClose(stmt);
			return null;
		}
		
		if (!connectionManager.commitAndClose(stmt)){
			return null;
		}
		
		return result;
	}
	
	public Map<String,String> searchByCategory(String category){ 
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt == null){
			return null;
		}
		List<String> cidList = ((CodeCategoriesTable )codeCategoriesTable).getCIDbyCategories(stmt, category);
		Map<String,String> cidsAndTitle = ((CodeTitleTable)codeTitleTable).getTitlesByCids(stmt, cidList);
		connectionManager.commitAndClose(stmt);
		return cidsAndTitle;
	}
	

	public boolean containsTitle(String title) {
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt == null){
			return false;
		}
		if (((CodeTitleTable)codeTitleTable).searchByTitle(stmt, title) != null){
			return true;
		}		
		return false;
	}

	/**
	 * 
	 * @param title
	 * @return -1 as fault value
	 */
	public int getLastVersion(String title) {
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt == null){
			return -1;
		}
		String cid = ((CodeTitleTable)codeTitleTable).searchByTitle(stmt, title);
		if (cid == null){
			// title doesn't exist
			return -1;
		}
		return ((CodeTable)codeTable).getLastVersion(stmt, cid);
	}

	public Document searchByTitle(String title, String uid, boolean isAdmin) {
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt == null){
			return null;
		}
		String cid = ((CodeTitleTable)codeTitleTable).searchByTitle(stmt, title);
		if (cid == null){
			// title doesn't exist
			connectionManager.rollbackAndClose(stmt);
			return null;
		}		
		try{
			String adminPart = "";
			if (!isAdmin)
			{
				adminPart = " AND (CTT.visibility='false' OR CTT.uid ='" + uid +"')";
			}
			String query = "SELECT version, visibility, UIT.uid as uid, firstName, lastName, admin, body, language" +
			" FROM " + codeTable.getName() + " CTT, " + UserDBMngr.getInstance().getUserInfoTable().getName() + " UIT" +
			" WHERE CTT.cid = '" + cid + "' AND CTT.uid = UIT.uid"+ adminPart + " ORDER BY CTT.version ASC;";
			ResultSet codeRs = (stmt.executeQuery(query));
			List<Version> versionList = createVersionList(codeRs);
			List<String> categoryList = ((CodeCategoriesTable)codeCategoriesTable).getCategories(stmt, cid);
			if (versionList == null || categoryList == null){
				// problem in finding category
				connectionManager.rollbackAndClose(stmt);
				return null;
			}
			connectionManager.commitAndClose(stmt);
			return createDomDocument(cid, title, versionList, categoryList);
		} catch(NullPointerException e){
			connectionManager.rollbackAndClose(stmt);
			return null;
		} catch (SQLException e){
			connectionManager.rollbackAndClose(stmt);
			return null;
		} catch (ParserConfigurationException e) {
			return null;
		}
	}

	private List<Version> createVersionList(ResultSet rs) {
		if (rs == null){
			return null;
		}
		List<Version> res = new ArrayList<Version>();
		try{
		while (rs.next())
		{
			res.add(new Version(rs.getString("version"),
					            Boolean.parseBoolean(rs.getString("visibility")),
					            rs.getString("body"),
					            rs.getString("language"),
					            rs.getString("uid"),
					            rs.getString("firstName"),
					            rs.getString("lastName"),
					            Boolean.parseBoolean(rs.getString("admin"))));
		}
		}catch (SQLException e) {
			return null;
		}
		return res;
	}

	public Document createDomDocument(String cid, String title, List<Version> versionList,
			List<String> categoryList) throws ParserConfigurationException {
		DocumentBuilderFactory dfactory = DocumentBuilderFactory.newInstance();
		dfactory.setValidating(true);
		DocumentBuilder dBuilder = dfactory.newDocumentBuilder();
		Document doc = dBuilder.newDocument();
		
		// Create the root element
		Element root = doc.createElement("document");
		root.setAttribute("cid", cid);
		doc.appendChild(root);
		
		// adding the title element
		Element titleElement = doc.createElement("title");
		titleElement.setTextContent(title);
		root.appendChild(titleElement);
		
		// creating categories elements
		for (String catregory : categoryList) {
			Element categoryElement = doc.createElement("category");
			categoryElement.setTextContent(catregory);
			root.appendChild(categoryElement);
		}
				
		// create version elements
		for (Version version : versionList) {
			Element versionElement = doc.createElement("version");
			versionElement.setAttribute("vernum", version.getVersionId());
			versionElement.setAttribute("visible", String.valueOf(version.isVisibile()));
			
			Element authorElement = doc.createElement("author");
			authorElement.setAttribute("uid", version.getUserId());
			authorElement.setAttribute("admin", String.valueOf(version.isAdministrator()));
			
			String firstName = version.getFirstName();
			String lastName = version.getLastName();
			if (!firstName.equals(""))
			{
				Element firstNameElement = doc.createElement("firstName");
				firstNameElement.setTextContent(firstName);
				authorElement.appendChild(firstNameElement);
			}
			if (!lastName.equals(""))
			{
				Element lastNameElement = doc.createElement("lastName");
				lastNameElement.setTextContent(lastName);
				authorElement.appendChild(lastNameElement);
			}
			versionElement.appendChild(authorElement);
			
			Element codeElement = doc.createElement("code");
			codeElement.setAttribute("language", version.getLanguage());
			codeElement.setTextContent(version.getCode());
			
			versionElement.appendChild(codeElement);			
			
			root.appendChild(versionElement);	
		}
		return doc;
	}

	public String searchCodeByCidVersion(String cid, String version) {
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt == null){
			return null;
		}
		String code = ((CodeTable)codeTable).searchByCidVersion(stmt, cid, version);
		connectionManager.commitAndClose(stmt);
		return code;
	}

	public Map<String, String> search(String inTitle, String category, String inCode, int page) {
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt == null){
			return null;
		}
		int fromItem = (page - 1) * ITEMS_IN_PAGE;
		int toItem = page * ITEMS_IN_PAGE;

		SortedMap<String, String> result = new TreeMap<String, String>();
		
		String andAndQuery = createQuery(inTitle, category, inCode, "AND", "AND");
		String andOrQuery = createQuery(inTitle, category, inCode, "AND", "OR");
		String orAndQuery = createQuery(inTitle, category, inCode, "OR", "AND");
		String orOrQuery = createQuery(inTitle, category, inCode, "OR", "OR");
		
		String finalQuery = andAndQuery+ " UNION " 
		                  + andOrQuery + " UNION "
		                  + orAndQuery + " UNION "
		                  + orOrQuery + " LIMIT " + fromItem +" , " + (toItem +1) + ";";
		ResultSet rs;
		try {
			rs = stmt.executeQuery(finalQuery);
			while(rs.next()){
				result.put((String)rs.getObject("cid"), (String)rs.getObject("title"));
			}
		} catch (SQLException e) {
			connectionManager.rollbackAndClose(stmt);
			return null;
		}
		
		if (!connectionManager.commitAndClose(stmt)){
			return null;
		}
		
		return result;
	}

	private String createQuery(String inTitle, String category, String inCode, String op1, String op2) {
		String select = "(SELECT DISTINCT CT.CID AS cid, CT.title AS title";
		
		// FROM & WHERE part
		String from = " FROM codesharingdb.codetitles CT ";
		String where = " WHERE";
		if (!category.equals("")) {
			from += ", codesharingdb.codecategories CC";
			where += " (CC.cid =CT.cid AND MATCH(CC.category) AGAINST('" + category + "' IN BOOLEAN MODE))";
		}
		if (!inCode.equals("")) {
			from += ", codesharingdb.codes CODES";
			if (!category.equals("")) {
				where += " " + op1;
			}
			where += " (CODES.cid = CT.CID AND CODES.body LIKE '%" + inCode + "%')";
		}
		if (!inTitle.equals("")) {
			// CT is already in from section
			if ((!category.equals("")) || (!inCode.equals(""))) {
				where += " " + op2;
			}
			where += " CT.title LIKE '%" + inTitle + "%'";
		}		

		String query = select + from + where + " GROUP BY cid ORDER BY cid) ";
		return query;
	}
	public String isVisible(String cid, String version){
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt == null){
			return null;
		}
		String query = "SELECT visibility FROM " + codeTable.getName() + " WHERE cid='" + cid + 
		"' AND version='" + version + "';";
		ResultSet rs;
		try {
			rs = stmt.executeQuery(query);
			if (rs.next()){
				String res =(String) rs.getObject("visibility"); 
				connectionManager.commitAndClose(stmt);
				return res;
			}
			connectionManager.commitAndClose(stmt);
			return null; // there isn't any visibility
		} catch (SQLException e) {
			connectionManager.rollbackAndClose(stmt);
			return null;
		}
	}

	public boolean changeVisibility(String cid, String version) {
		String visibility = isVisible(cid, version);
		Boolean b_visibility = Boolean.valueOf(visibility);
		b_visibility = !b_visibility;
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt == null){
			return false;
		}
		String query = "UPDATE " + codeTable.getName() + " SET visibility='" + b_visibility.toString() +
		"' WHERE cid='" + cid + "' AND version='" + version + "';";
		try{
			stmt.execute(query);
			connectionManager.commitAndClose(stmt);
			return true;
		} catch(SQLException e){
			connectionManager.rollbackAndClose(stmt);
			return false;
		}
	}

	public boolean deleteCode(String cid, String version) {
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_SERIALIZABLE);
		if (stmt == null){
			return false;
		}
		String query = "DELETE FROM " + codeTable.getName() + " WHERE cid='" + cid + "' AND version='" +
		version + "';";
		try{
			stmt.execute(query);
			connectionManager.commitAndClose(stmt);
		}	catch(SQLException e) {
			connectionManager.rollbackAndClose(stmt);
			return false;
		}
		Statement stmt2 = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt2 == null){
			return false;
		}
		query = "SELECT * FROM " + codeTable.getName() + " WHERE cid='" + cid + "';";
		try{
			ResultSet rs = stmt2.executeQuery(query);
			if (rs.next()){
				connectionManager.commitAndClose(stmt2);
				return true;
			}
		} catch(SQLException ex){
			connectionManager.rollbackAndClose(stmt2);
			return false;
		}
		// delete from codeTitle and codeCategories
		Statement stmt3 = connectionManager.getStatement(Connection.TRANSACTION_SERIALIZABLE);
		if (stmt3 == null){
			return false;
		}
		String query1 = "DELETE FROM " + codeTitleTable.getName() + " WHERE cid='" + cid + "';";
		String query2 = "DELETE FROM " + codeCategoriesTable.getName() + " WHERE cid='" + cid + "';";
		try{
			stmt3.execute(query1);
			stmt3.execute(query2);
			connectionManager.commitAndClose(stmt3);			
			return true;
		} catch(SQLException exc ){
			connectionManager.rollbackAndClose(stmt3);
			return false;
		}
	}

	public boolean duplicatVersion(String cid, String version, String code) {
		Statement stmt1 = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt1 == null){
			return false;
		}
		String title;
		LinkedList<String> values = new LinkedList<String>();
		
		title = ((CodeTitleTable)codeTitleTable).getTitle(stmt1,cid);
		if (title == null)
		{
			connectionManager.rollbackAndClose(stmt1);
			return false;
		}
		connectionManager.commitAndClose(stmt1);

		Statement stmt2 = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt2 == null){
			return false;
		}
		String query = "SELECT uid, language, visibility FROM " + codeTable.getName() + " WHERE cid='" + cid +
		                "' AND version='"+ version +"';";
		try{
			ResultSet rs = stmt2.executeQuery(query);
			if (rs.next()){	
				values.add(cid);
				String lang = (String)rs.getObject("language");
				String uid = (String)rs.getObject("uid");
				String vis = (String)rs.getObject("visibility");
				values.add(String.valueOf(getLastVersion(title)+1));
				values.add(uid);
				values.add(lang);
				values.add(code);
				values.add(vis);
				connectionManager.commitAndClose(stmt2);
			} 
			else
			{
				connectionManager.rollbackAndClose(stmt2);
				return false;				
			}
		} catch (SQLException e) {
			connectionManager.rollbackAndClose(stmt2);
			return false;
		}
		
		Statement stmt3 = connectionManager.getStatement(Connection.TRANSACTION_SERIALIZABLE);
		if (stmt3 == null){
			return false;
		}
		try {
			codeTable.insertRecord(stmt3, values);
			connectionManager.commitAndClose(stmt3);
			return true;
		} catch (SQLException e) {
			connectionManager.rollbackAndClose(stmt3);
			return false;
		}
	}
	public boolean getAllowence(String cid, String version, String uid){
		boolean admin = UserDBMngr.getInstance().isAdmin(uid);
		if (admin) {
			
			return true;
		}
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		if (stmt == null){
			return false;
		}
		boolean owner = ((CodeTable)codeTable).isOwner(stmt, cid, version, uid);
		connectionManager.commitAndClose(stmt);
		return owner;
	}
	public List<CidTitleVersion> getCodesByUser(String uid){
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		List<CidTitleVersion> res = new LinkedList<CidTitleVersion>();
		if (stmt == null){
			return null;
		}
		String query = "SELECT CODES.cid as cid, title, version FROM " + codeTable.getName() + " CODES, " + codeTitleTable.getName() +
			" CTT WHERE CODES.cid=CTT.cid AND uid='" + uid + "';";
		try{
			ResultSet rs = stmt.executeQuery(query);
			while(rs.next()){
				String cid = (String) rs.getObject("cid");
				String title = (String) rs.getObject("title");
				String version = (String) rs.getObject("version");
				res.add(new CidTitleVersion(cid, title, version));				
			}
			connectionManager.commitAndClose(stmt);
			return res;
		} catch(SQLException e){
			connectionManager.rollbackAndClose(stmt);
			return null;
		}
	}
	public List<VersionUidLangCid> getDetails(String cid){
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_READ_COMMITTED);
		List<VersionUidLangCid> res = new LinkedList<VersionUidLangCid>();
		if (stmt == null){
			return null;
		}
		String query = "SELECT cid, version, uid, language FROM " + codeTable.getName() + " WHERE cid='" + cid + "';";
		try{
			ResultSet rs = stmt.executeQuery(query);
			while(rs.next()){
				String version = (String) rs.getObject("version");
				String uid = (String) rs.getObject("uid");
				String language = (String) rs.getObject("language");
				res.add(new VersionUidLangCid(version, uid, language, cid));				
			}
			connectionManager.commitAndClose(stmt);
			return res;
		} catch(SQLException e){
			connectionManager.rollbackAndClose(stmt);
			return null;
		}	
	}
}
